using System;
using System.Text;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Web.Mvc.Ajax;
using MySql.Data.MySqlClient;
using System.Data;
using System.Configuration;

namespace TestProject
{
	public class Book
	{
		private int m_book_ID = 0;
		private string m_title;
		private string m_author;
		private string m_subject;
		
		public Book()
		{
		}
		
		public Book(int book_ID)
		{
			if (book_ID!=0)
			{
				this.m_book_ID = book_ID;
				getData();
			}
		}
		
		public int book_ID
		{
			set {
				m_book_ID = value;
				getData();
			}
			get {
				return m_book_ID;
			}
		}
		
		public string title
		{
			set {
				m_title = value;
			}
			get {
				return m_title;
			}
		}
		
		public string author
		{
			set {
				m_author = value;
			}
			get {
				return m_author;
			}
		}
		
		public string subject
		{
			set {
				m_subject = value;
			}
			get {
				return m_subject;
			}
		}
		
		private void getData()
		{
	        MySqlConnection conn = new MySqlConnection(Constants.connectionString);
	        try
			{
				conn.Open();
				string statement = "SELECT title, author, subject "
					+ "FROM users WHERE book_id = '" + this.m_book_ID + "'";
		        MySqlCommand cmd = new MySqlCommand(statement, conn);			
		        MySqlDataReader reader = cmd.ExecuteReader();
		        while(reader.Read()) {
					m_title = (string) reader["title"];
					m_author = (string) reader["author"];
					m_subject = (string) reader["subject"];
		        }
			}
			catch(Exception e)
			{
				Console.WriteLine(e.ToString());
			}
		    conn.Close();
		}
		
		public static void addBook(string title, string author, string subject)
		{
			MySqlConnection conn = new MySqlConnection(Constants.connectionString);
	        try
			{
				conn.Open();
				string newStatement = "INSERT INTO books (title, author, subject) "
					+ "VALUES ('"+title+"','"+author+"','"+subject+"')";
		        MySqlCommand newCmd = new MySqlCommand(newStatement, conn);
				newCmd.ExecuteNonQuery();
			}
			catch(Exception e)
			{
				Console.WriteLine(e.ToString());
			}
		    conn.Close();
		}

		public static void editBook(int book_ID, string title, string author, string subject)
		{
			MySqlConnection conn = new MySqlConnection(Constants.connectionString);
	        try
			{
				string newStatement = "UPDATE books SET title='"+title+"', author='"+author+"', subject='"+subject
					+ "' WHERE book_ID='"+book_ID+"'";
		        MySqlCommand newCmd = new MySqlCommand(newStatement, conn);
				newCmd.ExecuteNonQuery();
			}
			catch(Exception e)
			{
				Console.WriteLine(e.ToString());
			}
		    conn.Close();
		}

		public static List<Book> getBooks()
		{
			List<Book> booksList = new List<Book>(0);
			MySqlConnection conn = new MySqlConnection(Constants.connectionString);
	        try
			{
				conn.Open();
				string statement = "SELECT book_id "
					+ "FROM books";
		        MySqlCommand cmd = new MySqlCommand(statement, conn);			
		        MySqlDataReader reader = cmd.ExecuteReader();
		        while(reader.Read()) {
					Book current = new Book((int) reader["book_id"]);
					booksList.Add(current);
		        }
			}
			catch(Exception e)
			{
				Console.WriteLine(e.ToString());
			}
		    conn.Close();
			return booksList;
		}

		public static void deleteBook(int book_ID)
		{
			MySqlConnection conn = new MySqlConnection(Constants.connectionString);
	        try
			{
				conn.Open();				
				string statement = "DELETE FROM books where book_ID='"+book_ID+"'";
		        MySqlCommand cmd = new MySqlCommand(statement, conn);
				cmd.ExecuteNonQuery();				
				string newStatement = "DELETE FROM loan where book_ID='"+book_ID+"'";
		        MySqlCommand newCmd = new MySqlCommand(newStatement, conn);
				newCmd.ExecuteNonQuery();				
				string newStatementAgain = "DELETE FROM loan where book_ID='"+book_ID+"'";
		        MySqlCommand newCmdAgain = new MySqlCommand(newStatementAgain, conn);
				newCmdAgain.ExecuteNonQuery();
			}
			catch(Exception e)
			{
				Console.WriteLine(e.ToString());
			}
		    conn.Close();
		}
		
		public static List<Book> searchBooks(string title, string author, string subject)
		{
			StringBuilder statement = new StringBuilder("SELECT book_id FROM books WHERE");
			statement.Append(" title LIKE '%" + title + "%',");
			statement.Append(" author LIKE '%" + author + "%',");
			statement.Append(" subject LIKE '%" + subject + "%',");
			List<Book> booksList = new List<Book>(0);
			MySqlConnection conn = new MySqlConnection(Constants.connectionString);
	        try
			{
				conn.Open();
		        MySqlCommand cmd = new MySqlCommand(statement.ToString(), conn);			
		        MySqlDataReader reader = cmd.ExecuteReader();
		        while(reader.Read()) {
					Book current = new Book((int) reader["book_id"]);
					booksList.Add(current);
		        }
			}
			catch(Exception e)
			{
				Console.WriteLine(e.ToString());
			}
		    conn.Close();
			return booksList;
		}
	}
}


	